package work.database.example.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import work.database.example.connector.CloserUtil;
import work.database.example.dto.Department;

public class DepartmentDao implements InterfaceDao<Department> {
	
	private Connection conn;
	
	public DepartmentDao(Connection conn) {
		this.conn = conn;
	}

	@Override
	public void insertItem(Department item) throws SQLException {
		String sql = "insert department into values (?, ?, ?)";
		PreparedStatement pstmt = conn.prepareStatement(sql);
		pstmt.setInt(1, item.getDeptNo());
		pstmt.setString(2, item.getDeptName());
		pstmt.setInt(3, item.getFloor());
		pstmt.executeUpdate();
		CloserUtil.close(pstmt);		
	}

	@Override
	public void deleteItembyNo(Department item) throws SQLException {
		String sql = "delete from department where deptno=?";
		PreparedStatement pstmt = conn.prepareStatement(sql);
		pstmt.setInt(1, item.getDeptNo());
		pstmt.executeUpdate();
		CloserUtil.close(pstmt);
	}

	@Override
	public void updateItembyNo(Department item) throws SQLException{
		String sql = "update department set deptno = ?, deptname = ?, floor = ?";
		PreparedStatement pstmt = conn.prepareStatement(sql);
		pstmt.setInt(1, item.getDeptNo());
		pstmt.setString(2, item.getDeptName());
		pstmt.setInt(2, item.getFloor());
		pstmt.executeUpdate();
		CloserUtil.close(pstmt);
	}
	@Override
	public ArrayList<Department> listItems() throws SQLException {
		ArrayList<Department> list = new ArrayList<>();
		String sql = "select * from department";
		PreparedStatement pstmt = conn.prepareStatement(sql);
		ResultSet rs = pstmt.executeQuery();
		while(rs.next()){
			list.add(new Department(rs.getInt("deptno"), rs.getString("deptname"), rs.getInt("floor")));
		}
		return list;
	}
	
	@Override
	public Department searchItem(Department item) throws SQLException {
		Department dept = null;
		String sql = "select * from department where deptno=?";
		PreparedStatement pstmt = conn.prepareStatement(sql);
		pstmt.setInt(1, item.getDeptNo());
		
		ResultSet rs = pstmt.executeQuery();
		while(rs.next()){
			dept = new Department(rs.getInt(1), rs.getString(2), rs.getInt(3));			
		}
		return dept;		
	}
}
